package persistence.Impl;

import domain.Diary;
import domain.Item;
import domain.Product;
import persistence.ItemDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @projectName: petStore
 * @package: persistence.Impl
 * @className: ItemDAOImpl
 * @author: suqianbei
 * @description: TODO
 * @date: 2021/10/31 10:20
 * @version: 1.0
 */
public class ItemDAOImpl implements ItemDAO {
    private static final String updateInventoryQuantity=" UPDATE INVENTORY SET QTY =QTY-? WHERE ITEMID = ?";
    private static final String getInventoryQuantity="SELECT QTY AS value FROM INVENTORY WHERE ITEMID = ?";
    private static final String getItemListByProduct = "SELECT I.ITEMID,LISTPRICE,UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID AS \"product.productId\",NAME AS \"product.name\",DESCN AS \"product.description\",CATEGORY AS \"product.categoryId\",STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM ITEM I, PRODUCT P WHERE P.PRODUCTID = I.PRODUCTID AND I.PRODUCTID = ?";
    private static final String getItemByItemId="SELECT I.ITEMID, LISTPRICE, UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID AS \" product.productId \",NAME AS \" product.name \", DESCN AS  \"product.description \",CATEGORY AS \" product.categoryId \",STATUS,ATTR1  AS  attribute1 ,ATTR2 AS  attribute2 ,ATTR3 AS  attribute3 ,ATTR4 AS attribute4,ATTR5 AS attribute5  from ITEM I, INVENTORY V, PRODUCT P where P.PRODUCTID = I.PRODUCTID and I.ITEMID = V.ITEMID and I.ITEMID = ?";
    private static final String getItemListByKeyword="SELECT I.ITEMID,LISTPRICE,UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID AS \"product.productId\",NAME AS \"product.name\",CATEGORY AS \"product.categoryId\",STATUS FROM ITEM I, PRODUCT P WHERE PRODUCTID like ?";

    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(updateInventoryQuantity);
            String itemId=param.keySet().iterator().next();
            Integer increment=(Integer) param.get(itemId);
            preparedStatement.setInt(1,increment.intValue());
            preparedStatement.setString(2,itemId);
            preparedStatement.executeUpdate();
            DBUtil.close(connection,preparedStatement,null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int getInventoryQuantity(String itemId) {
        int qty=-1;
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getInventoryQuantity);
            preparedStatement.setString(1,itemId);
            ResultSet resultSet= preparedStatement.executeQuery();
            if(resultSet.next())
            {
                qty=resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return qty;
    }

    @Override
    public  List<Item> getItemListByProduct(String productId) {
        List<Item> itemList=new ArrayList<>();
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(getItemListByProduct);
            preparedStatement.setString(1,productId);
            ResultSet resultSet= preparedStatement.executeQuery();
            while (resultSet.next())
            {
                Item item=new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                item.setProductId(resultSet.getString(5));
                Product product=new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                itemList.add(item);
            }
            DBUtil.close(connection,preparedStatement,resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return itemList;
    }

    @Override
    public Item getItem(String itemId) {
        Item item=null;
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(getItemByItemId);
            preparedStatement.setString(1,itemId);
            ResultSet resultSet= preparedStatement.executeQuery();
            if (resultSet.next())
            {
                item=new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                item.setProductId(resultSet.getString(5));
                Product product=new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
            }
            DBUtil.close(connection,preparedStatement,resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return item;
    }

    @Override
    public List<Item> getItemListByKeyword(String keyword) {
        List<Item> itemList=new ArrayList<>();
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(getItemListByKeyword);
            preparedStatement.setString(1,keyword);
            ResultSet resultSet= preparedStatement.executeQuery();
            while (resultSet.next())
            {
                Item item=new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                item.setProductId(resultSet.getString(5));
                Product product=new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setCategoryId(resultSet.getString(7));
                item.setProduct(product);
                itemList.add(item);
            }
            DBUtil.close(connection,preparedStatement,resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return itemList;
    }

}
